#include "PQUtil.h"
#include <iostream>
#include <filesystem>
#include <json/forwards.h>
#include <json/value.h>
#include <string>
#include <tuple>
#include "PdfUtil.h"
#include "MuDraw.h"
void PQUtil::test(){
    try{
        // Connect to the database.
        pqxx::connection C;
        std::cout << "Connected to " << C.dbname() << '\n';

        // Start a transaction.
        pqxx::work W{C};

        // Perform a query and retrieve all results.
        pqxx::result R{W.exec("SELECT name FROM employee")};

        // Iterate over results.
        std::cout << "Found " << R.size() << "employees:\n";
        for (auto row: R)
            std::cout << row[0].c_str() << '\n';

        // Perform a query and check that it returns no result.
        std::cout << "Doubling all employees' salaries...\n";
        W.exec0("UPDATE employee SET salary = salary*2");

        // Commit the transaction.
        W.commit();
    }
    catch (std::exception const &e)
    {
        std::cerr << e.what() << '\n';
    }
}

void PQUtil::updateCppDoc(std::string& path,std::vector<std::tuple<std::string,std::string>> codes){
    std::filesystem::path p(path);
    auto file = p.filename().string();
    try{
        pqxx::connection C;
        pqxx::work W{C};

        //pqxx::result R{W.exec("SELECT name FROM employee")};
        C.prepare("delete", "delete from cppdoc where path = $1");
        W.exec_prepared("delete",path );

        C.prepare("insert", "insert into cppdoc(path,name,describe,code) values($1,$2,$3,$4)");

        for(auto desc_code :codes){
            auto [desc,code] = desc_code;
            W.exec_prepared("insert",path,file,desc,code );
        }

        W.commit();
        std::cout << "OK.\n";
    }
    catch (std::exception const &e)
    {
        std::cerr << e.what() << '\n';
    }
}

void PQUtil::updatePsql(std::string& path,std::vector<std::tuple<std::string,std::string>> codes){
    std::filesystem::path p(path);
    auto file = p.filename().string();
    try{
        pqxx::connection C;
        pqxx::work W{C};

        //pqxx::result R{W.exec("SELECT name FROM employee")};
        C.prepare("delete", "delete from psql where path = $1");
        W.exec_prepared("delete",path );

        C.prepare("insert", "insert into psql(path,name,describe,code) values($1,$2,$3,$4)");

        for(auto desc_code :codes){
            auto [desc,code] = desc_code;
            W.exec_prepared("insert",path,file,desc,code );
        }

        W.commit();
        std::cout << "OK.\n";
    }
    catch (std::exception const &e)
    {
        std::cerr << e.what() << '\n';
    }
}

void PQUtil::updateRust(std::string& path,std::vector<std::string> codes){
    std::filesystem::path p(path);
    auto file = p.filename().string();
    try{
        pqxx::connection C;
        pqxx::work W{C};

        //pqxx::result R{W.exec("SELECT name FROM employee")};
        C.prepare("delete", "delete from rust where path = $1");
        W.exec_prepared("delete",path );

        C.prepare("insert", "insert into rust(path,name,describe,code) values($1,$2,$3,$4)");

        std::string desc = "";
        for(auto code :codes){
            W.exec_prepared("insert",path,file,desc,code );
        }

        W.commit();
        std::cout << "OK.\n";
    }
    catch (std::exception const &e)
    {
        std::cerr << e.what() << '\n';
    }
}


void PQUtil::updateCpp(std::string& path,std::vector<std::string> codes){
    std::filesystem::path p(path);
    auto file = p.filename().string();
    try{
        pqxx::connection C;
        pqxx::work W{C};

        //pqxx::result R{W.exec("SELECT name FROM employee")};
        C.prepare("delete", "delete from cpp where path = $1");
        W.exec_prepared("delete",path );

        C.prepare("insert", "insert into cpp(path,name,describe,code) values($1,$2,$3,$4)");

        std::string desc = "";
        for(auto code :codes){
            W.exec_prepared("insert",path,file,desc,code );
        }

        W.commit();
        std::cout << "OK.\n";
    }
    catch (std::exception const &e)
    {
        std::cerr << e.what() << '\n';
    }
}

void PQUtil::updateCppPdf(std::string& path,std::string& table){
    std::filesystem::path p(path);
    auto file = p.filename().string();
    try{
        pqxx::connection C;
        pqxx::work W{C};

        //pqxx::result R{W.exec("SELECT name FROM employee")};
        C.prepare("delete", "delete from "+table+" where path = $1");
        W.exec_prepared("delete",path );

        C.prepare("insert", "insert into "+table+"(path,name,describe,code,level,file_type,cid,pcid) values($1,$2,$3,$4,$5,$6,$7,$8)");

        auto tocs = PdfUtil::getPdfToc(path.c_str());
        for(auto toc :tocs){
            int level = toc.get_level();
            std::string code  = toc.get_page();
            std::string desc = toc.get_title();
            int cid = toc.get_cid();
            int pcid = toc.get_pcid();
            W.exec_prepared("insert",path,file,desc,code,level ,"pdf",cid,pcid);
        }

        W.commit();
        std::cout << "OK.\n";
    }
    catch (std::exception const &e)
    {
        std::cerr << e.what() << '\n';
    }
}

void PQUtil::updateVim(std::string& path,std::vector<std::string> codes){
    std::filesystem::path p(path);
    auto file = p.filename().string();
    try{
        pqxx::connection C;
        pqxx::work W{C};

        //pqxx::result R{W.exec("SELECT name FROM employee")};
        C.prepare("delete", "delete from vim where path = $1");
        W.exec_prepared("delete",path );

        C.prepare("insert", "insert into vim(path,name,describe,code) values($1,$2,$3,$4)");

        std::string desc = "";
        for(auto code :codes){
            W.exec_prepared("insert",path,file,desc,code );
        }

        W.commit();
        std::cout << "OK.\n";
    }
    catch (std::exception const &e)
    {
        std::cerr << e.what() << '\n';
    }
}

std::string lang_for_table(std::string& table){
    if(table=="cpp"){
        return "cpp";
    }else if(table=="psql"){
        return "sql";
    }else{
        return table;
    }

}
std::vector<std::string>& getPage(std::string& path,std::string& page){
   char**  argv= new char*[7];
   argv[0] = "mutool";argv[1]="-F";argv[2]="png";
   argv[3] = "-r";argv[4]="150";
   argv[5] = new char[256];strcpy(argv[5], path.c_str());
   argv[6] = new char[50];strcpy(argv[6],page.c_str());
   return mudraw_main(7, argv);
}

std::string get_pdf_page(Json::Value path,Json::Value page){
    std::string path_s = path.asString();
    std::string page_s = page.asString();
    auto rets = getPage(path_s, page_s);
    return rets[0];
}

void getResult(Json::Value& array, pqxx::result& R, std::string& table,bool is_pdf = false){

    std::vector<std::string> names;
    if(R.size()>0){
        auto first = R[0];
        for(auto column:first){
            names.push_back(column.name());
        }
        for (auto row: R){
            Json::Value*  obj = new Json::Value(Json::objectValue);
            int i = 0;
            for(auto col :row){
                auto key = names.at(i);
                std::string val(col.c_str());
                (*obj)[key] = val;
                i++;
            }
            (*obj)["lang"] = lang_for_table(table);
            (*obj)["table"] = table;
            if(is_pdf){
                (*obj)["html"] = get_pdf_page((*obj)["path"],(*obj)["code"]);
            }
            array.append(*obj);
        }
    }
    return ;
}

Json::Value PQUtil::readData(std::string& table,std::string& cond,std::string& page,std::string& size,std::string& type){
    try{
        // Connect to the database.
        pqxx::connection C;
        std::cout << "Connected to " << C.dbname() << '\n';

        // Start a transaction.
        pqxx::work W{C};
        std::string sql =  "SELECT count(*) FROM "+table + " where "+cond+" and file_type ilike '%"+type+"%'";
        std::cout<<"sql:"<<sql<<std::endl;
        pqxx::result T{W.exec(sql )};
        int total ;
        total = T.at(0)[0].as(total);
        std::cout<<"total:"<<total<<std::endl;

        int cur_page = 1;
        int page_size = 20;
        if(page!=""){ cur_page = atoi(page.c_str());}
        if(size!=""){ page_size = atoi(size.c_str());}
        // Perform a query and retrieve all results.
        std::string sel = "SELECT * FROM "+table+" where "+cond + " and file_type='file' and file_type ilike '%"+type+"%' limit "+std::to_string(page_size)+" offset "+std::to_string((cur_page-1)*page_size);
        std::cout<<"sel:"+sel<<std::endl;
        pqxx::result R{W.exec(sel)};

        // use half page_size
        page_size = page_size / 2.5;

        std::string sel2 = "SELECT * FROM "+table+" where "+cond + " and file_type='pdf' and file_type ilike '%"+type+"%' limit "+std::to_string(page_size)+" offset "+std::to_string((cur_page-1)*page_size);
        std::cout<<"sel2:"+sel2<<std::endl;
        pqxx::result R2{W.exec(sel2)};


        Json::Value array(Json::arrayValue);
        getResult(array,  R, table);

        Json::Value array2(Json::arrayValue);
        getResult(array2,  R2, table,true);

        // Commit the transaction.
        W.commit();

        Json::Value json;
        json["total"] = total;
        json["data"] = array;
        json["pdf"] = array2;
        return json;
    }
    catch (std::exception const &e)
    {
        Json::Value json(Json::objectValue);
        std::cerr << e.what() << '\n';
        return json;
    }
}

Json::Value PQUtil::searchPdfToc(std::string& path,std::string& table,std::string& cid,std::string& pcid){
    try{
        // Connect to the database.
        pqxx::connection C;
        std::cout << "Connected to " << C.dbname() << '\n';

        // Start a transaction.
        pqxx::work W{C};
        if(pcid==""){
            pcid="1";
        }
        std::string selp = "SELECT * FROM "+table+" where cid = "+ pcid +" and file_type='pdf' and path='"+path+"'";
        pqxx::result Rp{W.exec(selp)};
        std::string sel = "SELECT * FROM "+table+" where pcid = "+ pcid +" and file_type='pdf' and path='"+path+"'";
        pqxx::result R{W.exec(sel)};

        Json::Value array(Json::arrayValue);
        getResult(array,  Rp, table);
        getResult(array,  R, table);

        // Commit the transaction.
        W.commit();

        Json::Value json;
        json["data"] = array;
        json["path"] = path;
        return json;
    }
    catch (std::exception const &e)
    {
        Json::Value json(Json::objectValue);
        std::cerr << e.what() << '\n';
        return json;
    }
}


Json::Value PQUtil::searchPdfPage(std::string& path,std::string& table,std::string& page){
    try{
        // Connect to the database.
        pqxx::connection C;

        // Start a transaction.
        pqxx::work W{C};
        if(page==""){ page="1"; }
        std::string sel = "SELECT * FROM "+table+" where code = '"+ page +"' and file_type='pdf' and path='"+path+"' limit 1";
        std::cout<<"sel:"+sel<<std::endl;
        pqxx::result R{W.exec(sel)};

        Json::Value array(Json::arrayValue);
        getResult(array,  R, table,true);
        W.commit();

        std::vector<std::string>& htmls = getPage(path, page);
        if(array.size() > 0){
            // has data do nothing
        }else{
            // no data ,create one
            Json::Value*  obj = new Json::Value(Json::objectValue);
            (*obj)["lang"] = lang_for_table(table);
            (*obj)["table"] = table;
            (*obj)["path"] = path;
            (*obj)["code"] = page;
            (*obj)["html"] = htmls[0];
            array.append(*obj);
        }
        Json::Value json;
        json["data"] = array;
        return json;
    }
    catch (std::exception const &e)
    {
        Json::Value json(Json::objectValue);
        std::cerr << e.what() << '\n';
        return json;
    }
}

